package cn.com.lgyw.jdbc.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.Vector;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.com.lgyw.jdbc.bean.ConnectionBean;
import cn.com.lgyw.jdbc.transaction.TransactionUtil;

/**
 * 数据库连接池,用Map<String, Vector
 * <ConnectionBean>>将每种类型的连接进行缓存,键表示是哪种类型的连接,Vector数组中包含该类型下的所有连接.
 * 
 * 连接类型eg:北分中心的连接,中心平台的连接,站级系统的连接(每个站的IP都不一样)
 * 
 * @author guojian
 */

public class ConnectionPool {

	private static final Log log = LogFactory.getLog(ConnectionPool.class);
	// 最大连接池的容量
	private static final int MAX_CONNECTION_POOL_SIZE = 10;

	// 初始化连接池的容量大小
	private static final int INIT_CONNECTION_POOL_SIZE = 1;

	// 键代表连接池的类型
	private static final Map<String, Vector<ConnectionBean>> connectionPoolMap = new HashMap<String, Vector<ConnectionBean>>();

	private static final Map<String, String> urlTypeMap = new HashMap<String, String>();

	private static final Properties dbProperties = new Properties();

	static {

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
		}

		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
		}

		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e1) {
		}

		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e1) {
		}

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (Exception e) {
		}

	}

	/**
	 * 创建数据库连接
	 * 
	 * @param IP
	 * @param userName
	 * @param passWord
	 * @param DbName
	 * @param dbType
	 * @return
	 */
	private static ConnectionBean createConnection(String dburl, String userName, String passWord) {
		Connection con = null;
		String typeName = getTypeNameByDBurl(dburl);
		try {
			if (dburl.contains("mysql")) {
				con = DriverManager
						.getConnection(dburl + "?user=" + userName.trim() + "&password=" + passWord.trim() + "&useUnicode=true&characterEncoding=UTF8&socketTimeout=10000");
			} else if (dburl.contains("sqlserver") || dburl.contains("oracle")) {
				con = DriverManager.getConnection(dburl, userName.trim(), passWord.trim());
			} else if (dburl.contains("sqlite")) {
				con = DriverManager.getConnection(dburl);
			} else if (dburl.contains("Access")) {
				dbProperties.setProperty("user", userName);
				dbProperties.setProperty("password", passWord);
				dbProperties.setProperty("charSet", "gb2312");
				con = DriverManager.getConnection(dburl, dbProperties);
			} else {
				throw new RuntimeException("数据库连接不规范..");
			}
			log.debug("init connection: " + con);
		} catch (Exception e) {
			con = null;
			log.error("连接初始化失败: " + dburl, e);
		}
		return new ConnectionBean(con, typeName);
	}

	/**
	 * 
	 * 创建连接池
	 * 
	 * @param initPoolConnectionSize
	 *            (初始化连接池容量)
	 * @param IP
	 * @param userName
	 * @param passWord
	 * @param DbName
	 * @param dbType
	 * @param typeName
	 * @return
	 */
	private static Vector<ConnectionBean> createConnectionPool(int initPoolConnectionSize, String dburl, String userName, String passWord, String typeName) {
		log.debug("begin init connectionPool....." + typeName);
		Vector<ConnectionBean> connectionPool = new Vector<ConnectionBean>();
		for (int i = 0; i < initPoolConnectionSize; i++) {
			connectionPool.add(createConnection(dburl, userName, passWord));
		}
		log.debug("end init connectionPool....." + typeName);
		return connectionPool;

	}

	/**
	 * 
	 * 得到一个空闲的连接,如果当前连接处于繁忙状态,并且最大连接数没有超过,就重新在创建新的连接
	 * 
	 * @param IP
	 * @param userName
	 * @param passWord
	 * @param DbName
	 * @param dbType
	 * @param typeName
	 * @return
	 */
	public static synchronized ConnectionBean getFreeConnection(String dburl, String userName, String passWord) {
		String typeName = getTypeNameByDBurl(dburl);
		Vector<ConnectionBean> connectionPool = connectionPoolMap.get(urlTypeMap.get(dburl));

		if (connectionPool == null || connectionPool.size() == 0) {
			connectionPool = createConnectionPool(INIT_CONNECTION_POOL_SIZE, dburl, userName, passWord, typeName);
			connectionPoolMap.put(typeName, connectionPool);
		}

		// 循环所有连接得到空闲的连接
		for (int i = 0; i < connectionPool.size(); i++) {
			ConnectionBean conbean = connectionPool.get(i);
			if (!conbean.isBusy()) {
				conbean.setBusy(true);
				return conbean;
			}
		}
		// 如果所有连接都忙,但是最大连接数没有超过,就创建新的连接并加入到缓存
		if (connectionPool.size() < MAX_CONNECTION_POOL_SIZE) {
			ConnectionBean conBean = createConnection(dburl, userName, passWord);
			conBean.setBusy(true);
			connectionPool.add(conBean);
			connectionPoolMap.put(typeName, connectionPool);
			return conBean;
		}
		// 如果上面两种情况都不满足,就等待100ms,继续获取有效连接
		getConnectionForWait(100);
		return getFreeConnection(dburl, userName, passWord);
	}

	private static void getConnectionForWait(int millis) {
		try {
			Thread.sleep(millis);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 将连接的状态设置成空闲,没有关闭连接
	 * 
	 * @param con
	 * @param typeName
	 * @param result
	 * @param statement
	 */
	public static synchronized void closeConnection(Connection con, String typeName, ResultSet result, Statement statement) {
		if (result != null) {
			try {
				result.close();
			} catch (Exception e) {
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
			}
		}
		Vector<ConnectionBean> connectionPool = connectionPoolMap.get(typeName);
		if (connectionPool == null) {
			return;
		}
		Iterator<ConnectionBean> connectionBeanIt = connectionPool.iterator();
		while (connectionBeanIt.hasNext()) {
			ConnectionBean conbean = (ConnectionBean) connectionBeanIt.next();
			try {
				if (conbean.getCon() == null || conbean.getCon().isClosed()) {
					connectionPool.remove(conbean);
					continue;
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			if (conbean.getCon().equals(con)) {
				boolean exceptionState = TransactionUtil.exceptionState.get() == null ? false : true;
				try {
					if (exceptionState) {
						if (!con.isClosed()) {
							con.close();
						}
						connectionBeanIt.remove();
						continue;
					}
					conbean.setBusy(false);
					if (!con.getAutoCommit()) {
						con.setAutoCommit(true);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}
	}

	/**
	 * 关闭某个类型下的所有连接,并清空缓存
	 * 
	 * @param typeName
	 */
	public static synchronized void clearAllConnection(String typeName) {
		Vector<ConnectionBean> connectionPool = connectionPoolMap.get(typeName);
		if (connectionPool == null) {
			return;
		}
		try {
			for (ConnectionBean connectionBean : connectionPool) {
				Connection con = connectionBean.getCon();
				if (con != null) {
					if (!con.isClosed()) {
						con.close();
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		connectionPool.removeAllElements();// 将改类型下的所有连接全部清除

		System.out.println("clear all connetions of ...." + typeName);
	}

	private static String getTypeNameByDBurl(String dburl) {

		String typeName = urlTypeMap.get(dburl);
		if (StringUtils.isNotBlank(typeName)) {
			return typeName;
		}
		if (dburl.contains("mysql")) {
			typeName = dburl.substring(dburl.lastIndexOf("/") + 1);
		} else if (dburl.contains("sqlserver")) {
			typeName = dburl.substring(dburl.lastIndexOf("=") + 1);
		} else if (dburl.contains("oracle")) {
			typeName = dburl.substring(dburl.lastIndexOf(":") + 1);
		} else if (dburl.contains("sqlite") || dburl.contains("Access")) {
			int position = dburl.lastIndexOf("\\");
			if (position == -1) {
				position = dburl.lastIndexOf("/");
			}
			typeName = dburl.substring(position + 1);
		} else {
			throw new RuntimeException("数据库连接不规范..");
		}
		urlTypeMap.put(dburl, typeName);
		return typeName;
	}

	public static void main(String[] args) {

	}
}
